﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataCom;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Globalization;

namespace DataCommunication
{
    public class ProductInfoDataAccess : IProductInfoDataAccess
    {
        /// <summary>
        /// Get Product Info
        /// </summary>
        /// <param name="lineCode"></param>
        /// <param name="day"></param>
        /// <returns></returns>
        public DataTable GetProductInfo(string lineCode, string shift, string NumTime)
        {
            int rowAffected = 0;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;

            connection.Open();

            command.Parameters.Clear();

            if (string.IsNullOrWhiteSpace(lineCode))
            {
                command.CommandText = "select LineCode, Shift, ProductNum,NumTime from dbo.ProductInfo where Shift = @Shift and NumTime = @NumTime";

                command.Parameters.AddWithValue("@Shift", shift);
                command.Parameters.AddWithValue("@NumTime", NumTime);
            }
            else
            {
                command.CommandText = "select LineCode, Shift,  ProductNum,NumTime from dbo.ProductInfo where Shift = @Shift and NumTime = @NumTime and LineCode = @LineCode";

                command.Parameters.AddWithValue("@Shift", shift);
                command.Parameters.AddWithValue("@LineCode", lineCode);
                command.Parameters.AddWithValue("@NumTime", NumTime);
            }

            DataSet dsProductInfo = new DataSet();
            dsProductInfo.Locale = CultureInfo.InvariantCulture;
            rowAffected = dataAdapter.Fill(dsProductInfo);

            command.Parameters.Clear();
            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return dsProductInfo.Tables[0];
        }

        /// <summary>
        /// Update Product Info
        /// </summary>
        /// <param name="dtProductInfo"></param>
        /// <returns></returns>
        public bool UpdateProductInfo(DataTable dtProductInfo)
        {
            if ((dtProductInfo == null)
                || (dtProductInfo.Rows.Count <= 0))
            {
                return false;
            }

            bool result = true;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            connection.Open();

            try
            {
                command.Transaction = connection.BeginTransaction("Trans");

                foreach (DataRow row in dtProductInfo.Rows)
                {
                    if (row.RowState != DataRowState.Deleted
                        && row.RowState != DataRowState.Detached)
                    {
                        string lineCode = row["LineCode"].ToString();
                        string shift = row["Shift"].ToString();
                        long productNum = 0;
                        long.TryParse(row["ProductNum"].ToString(), out productNum);
                        string NumTime = row["NumTime"].ToString();

                        command.CommandText = "delete from dbo.ProductInfo where LineCode = @LineCode and Shift = @Shift and NumTime=@NumTime";

                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@LineCode", lineCode);
                        command.Parameters.AddWithValue("@Shift", shift);
                        command.Parameters.AddWithValue("@NumTime", NumTime);

                        command.ExecuteNonQuery();

                        command.CommandText = "insert into dbo.ProductInfo (LineCode, Shift, ProductNum,NumTime ) values (@LineCode, @Shift, @ProductNum,@NumTime)";

                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@LineCode", lineCode);
                        command.Parameters.AddWithValue("@Shift", shift);
                        command.Parameters.AddWithValue("@ProductNum", productNum);
                        command.Parameters.AddWithValue("@NumTime", NumTime);

                        command.ExecuteNonQuery();
                    }
                }

                command.Transaction.Commit();
            }
            catch (Exception ex)
            {
                result = false;

                try
                {
                    command.Transaction.Rollback();
                }
                catch (Exception ex2)
                {
                }
            }

            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return result;
        }
    }
}
